![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Viewing TriggersWhen triggers are added to tables, the definitions are added to the data dictionary. The constraint information is kept in internal tables in the data dictionary. These tables can be queried from several views. The views defined for constraint information are as follows:
In these tables, some of the important information concerning triggers is contained in the following columns:
This information can be useful when analyzing or debugging triggers.
Audit TrailsOracle has built-in auditing facilities you can use to create an audit trail of events that have occurred in the RDBMS. The audit trail is somewhat configurable and can contain as much or as little auditing information as you want. By increasing the number of events to be audited, you increase the overhead incurred by the RDBMS. It is wise to audit as little as you can get away with to reduce the impact on the system. Audit trail information can contain the following:
Use the AUDIT and NOAUDIT commands to turn auditing on and off on a per-object basis. The procedure for doing this, and the objects that can be modified, are explained in the Oracle documentation and are not covered here. Auditing is an important part of the security of your system, but you should limit it to only the most critical objects. Auditing involves significant use of system resources; by reducing the amount of auditing you do, you can lessen the impact on the system. If you are in a somewhat secure environment, you may not find auditing necessary. But if you are in a large organization with lots of activity on the RDBMS, you should seriously consider auditing. What to audit is a decision to be made on a case-by-case basis and cannot be taken lightly. As much as possible, limit your auditing to reduce overhead, but audit as much as you need to. If auditing is being done, the auditing information is kept in the AUD$ table. Check this table periodically to make sure that it does not fill up the SYSTEM tablespace. Serial ReadsIf your application requires serializable reads, you must change the parameters SERIALIZABLE and ROW_LOCKING in the Oracle initialization file. By changing these parameters, you alter the performance of the system dramatically. Oracle recommends that users implement applications using the default row locking. Oracle has put considerable effort into improving the performance of serializable transactions to compete with other RDBMS vendors in the TPC-C benchmark and has done very well. This does not mean that application developers are forced to use serializable transactions. Oracle row-level locking is suitable for almost every application. By setting the Oracle initialization parameter SERIALIZABLE to TRUE, queries acquire table-level read locks, preventing updates to the objects being read until the transaction containing the query has been committed. This mode provides for repeatable reads and ensures that two queries for the same data in the same transaction see the same data. This parameter provides for ANSI-degree-three consistency but at a considerable cost in concurrency. SummaryThis chapter presented several ways you can optimally provide for data integrity. When designing applications, you should take advantage of Oracle features such as integrity constraints and triggers where applicable. By using internal features such as integrity constraints, you can reduce unnecessary overhead in the application, the network, and the server. It is important that you do not sacrifice integrity for performance. A database that does not guarantee data integrity is not worth having. The database and the application are only as good as the data stored within them. Another topic covered in this chapter was the use of auditing. Although auditing may or may not be an important part of your operation, by taking advantage of only the auditing features you need and eliminating those you dont need, you can optimize performance and still track required information. Finally, the chapter briefly discussed the use of serializable reads, which some applications need for data integrity. I believe that a well-designed application that understands Oracle row locking can perform well and not sacrifice data integrity. The use of serializable transactions does not provide more data integrity, it just provides it in a different way than row locking. If at all possible, avoid the use of serializable reads in your database and application.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |